/**
 * This class will implement the controller of the robot.
 * It will calculate optimal parking location when entering car to the lot 
 * and optimal new  when removing car from the lot.
 * 
 * @author Gil Levinson
 * @author Boris Fradlis
 * @author Yogev Levi
 * @author Hila Porat  
 */

package system;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.sql.Time;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.Vector;
import java.io.Serializable;
import org.joda.time.DateTime;
import org.joda.time.DateTimeConstants;
import org.joda.time.Days;
import org.joda.time.format.DateTimeFormat;
import org.joda.time.format.DateTimeFormatter;

import vcpClasses.Order;
import vcpClasses.ParkingSpace;
import vcpClasses.SubscribedClient;
import vcpClasses.Vehicle;

// Start of RobotControl ************************************************************************

public class RobotControl {
	
	// Class variables ****************************************************************************
	
	private Statement stmt;
	private Order order;
	private SubscribedClient client;
	private DataBaseConnection db;
	int maxSid[][] = new int[6][3];
	int lot = 0;
		
	// Constructor **********************************************************************************
	
	/**
	 * Construct new robotControl with connection to DB
	 * @param conn - connection to database
	 */
	
	public RobotControl( DataBaseConnection db){
		int i,j;
		this.db = db;
		this.order = null;
		this.client = null;
	}
	
	// Instance Methods *******************************************************************************
	
	/**
	 * this method will fetch order from the DB, given only the vehicle number and the status of the order
	 * @param vehicle the number of the vehicle
	 * @param status the status of the order
	 * @return the order if found it in the db, null otherwise
	 * @throws SQLException if connection with data base was unsuccessful 
	 */
	
	private Order getOrder(int vehicle,String status) throws SQLException
	{
		ResultSet rs;
		Order o = new Order(vehicle);
		SimpleDateFormat thetime = new SimpleDateFormat("HH:mm:ss");
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE vehicleNumber = "+o.getVehicleNumber()+" AND orderStatus = '"+status+"';");
		if (rs.first())
		{
			o.setOrderNumber(rs.getInt(1));
			o.setOrderDate(rs.getDate(2).toString());
			o.setOrderTime(rs.getTime(3).toString());
			o.setOrderType(rs.getString(5));
			o.setExpectedLeaveTime(thetime.format(rs.getTime(8)));
			o.setOrderStatus(rs.getString(9));
			o.setClientNumber(rs.getInt(11));
			o.setVehicleNumber(rs.getInt(12));
			o.setSidParkingLocation(rs.getInt(13));
			o.setLeaveDate(rs.getString(14));
			if (o.getOrderStatus().equals("OCCASIONAL"))
				o.setArrivalTime(rs.getTime(4).toString());
			else
				o.setArrivalTime(thetime.format(new Date()));
			return o;
		}
		return null;
	}
	
	/**
	 * this method will fetch subscription from the DB, given only the vehicle number
	 * @param vehicle the vehicle number
	 * @param status the status of the subscription
	 * @return the subscription if found it in the db, null otherwise
	 * @throws SQLException if connection with data base was unsuccessful 
	 */
	
	
	private SubscribedClient getSubscription(int vehicle, String status) throws SQLException
	{
		ResultSet rs;
		SubscribedClient c = new SubscribedClient(vehicle);
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.subscribedclient WHERE vehicleNumber = "+vehicle+" AND subscriptionStatus = '"+status+"';");
		if (rs.first())
		{
			c.setStartDate(rs.getString(2));
			c.setClientLeaveTime(rs.getString(3));
			c.setTypeOfSubscription(rs.getString(4));
			c.setClientNumber(rs.getInt(5));
			c.setVehicleNumber(rs.getInt(6));
			c.setLotNumber(rs.getInt(7));
			c.setNumberOfParkingUsed(rs.getInt(8));
			c.setLastParkingDate(rs.getString(9));
			c.setSubsequentDays(rs.getInt(10));
			c.setSubscriptionStatus(rs.getString(11));
			c.setClientLeaveDate(rs.getString("ClientLeaveDate"));
			c.setSidParkingLoaction(rs.getInt("sidParkingLocation"));
			return c;
		}
		return null;
	}
	
	
	/**
	 * This method will check the validity of entering the car to the lot.
	 * It will prevent entering car of not approved order, not paid for order, invalid subscription and etc. 
	 * @param myType the subscription or order to process
	 * @return true if the car is able to park and false other wise 
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private SystemTypes CheckValidity(SystemTypes myType) throws SQLException
	{
		ResultSet rs;
		String msg= "";
		float balance;
		Serializable obj = myType.getObj();
		int i,j;
		for (i=0;i<6;i++)
			for(j=0;j<3;j++)
				maxSid[i][j] = this.findMaxFromOccupiedInRow(j+1, i+1);
		String type = "Order";
		if (!(obj instanceof Order))
		{
			type = "Subscritpion";
			lot = 1;//((SubscribedClient)obj).getLotNumber();
		}
		else
			lot = ((Order)obj).getSidParkingLocation();
		if (!db.isLotFull(lot))
		{
			msg = "Operation failed - the lot is full!";
			System.out.println(msg);
			myType.setObj(msg);
			myType.setSysMode(SystemMode.FAIL);
			return myType;
		}
		switch (type)
		{
			case "Order":
			{
				order = this.getOrder(((Order)obj).getVehicleNumber(),"OPEN");
				if (order == null) 
				{
					order = this.getOrder(((Order)obj).getVehicleNumber(), "ACTIVE");
					if (order == null)
						order = this.getOrder(((Order)obj).getVehicleNumber(), "PLANNED");
					if (order == null)
					{
						msg = "Operation failed - the car is allready in the lot!";
						System.out.println(msg);
						myType.setObj(msg);
						myType.setSysMode(SystemMode.EMPTY_RES);
						return myType;
					}
				}
				if (order.getOrderStatus().equals("OPEN")&&(order.getOrderType().equals("OCCASIONAL")))
				{
					msg = "Operation ended succesfully!";
					System.out.println(msg);
					myType.setObj(msg);
					myType.setSysMode(SystemMode.OK);
					return myType;
				}
				else 
					if (order.getOrderType().equals("OCCASIONAL"))
					{
						msg = "Operation failed - the order not open!";
						System.out.println(msg);
						myType.setObj(msg);
						myType.setSysMode(SystemMode.FAIL);
						return myType;
					}
				stmt = db.getConn().createStatement();
				rs = stmt.executeQuery("SELECT * FROM vcp.client WHERE clientNumber = "+order.getClientNumber()+";");
				rs.next();
				balance = rs.getFloat("clientBalance");
				if (order.getOrderStatus().equals("OPEN")||order.getOrderStatus().equals("PLANNED"))
				{
					if (balance < 0)
					{
						msg = "Operation failed - the order was not paid yet!";
						System.out.println(msg);
						myType.setObj(msg);
						myType.setSysMode(SystemMode.FAIL);
						return myType;
					}
					else
					{
						msg = "Operation ended succesfully!";
						System.out.println(msg);
						myType.setObj(msg);
						myType.setSysMode(SystemMode.OK);
						return myType;
					}
				}
			break;
			}
			case "Subscritpion":
			{
				client = this.getSubscription(((SubscribedClient)obj).getVehicleNumber(),"VALID");
				if (client == null)
				{
					msg = "Operation failed - the subscription number not exists!";
					System.out.println(msg);
					myType.setObj(msg);
					myType.setSysMode(SystemMode.EMPTY_RES);
					return myType;
				}
				
				if (client.getSubscriptionStatus().equals("VALID"))
				{
					rs = stmt.executeQuery("SELECT * FROM vcp.client WHERE clientNumber = "+client.getClientNumber()+";");
					rs.first();
					balance = rs.getFloat("clientBalance");
					if (balance < 0)
					{
						msg = "Operation failed - the subscription was not paid yet!";
						System.out.println(msg);
						myType.setObj(msg);
						myType.setSysMode(SystemMode.FAIL);
						return myType;
					}
					else
					{
						if (client.getTypeOfSubscription().equals("FULL"))
						{
							msg = "Operation ended succesfully!";
							System.out.println(msg);
							myType.setObj(msg);
							myType.setSysMode(SystemMode.OK);
							return myType;	
						}
						else
							{
								DateTime d1 = new  DateTime(client.getLastParkingDate());
								DateTime d2 = new DateTime();
								if (d1.weekOfWeekyear()!=d2.weekOfWeekyear())
								{
									if(d1.getDayOfWeek()!=  DateTimeConstants.FRIDAY && d1.getDayOfWeek() !=  DateTimeConstants.SATURDAY)
									{
										msg = "Operation ended succesfully!";
										System.out.println(msg);
										myType.setObj(msg);
										myType.setSysMode(SystemMode.OK);
										return myType;
									}
									else
									{
										msg = "Operation failed - Parking not allowed  on friday and saturdays!";
										System.out.println(msg);
										myType.setObj(msg);
										myType.setSysMode(SystemMode.EMPTY_RES);
										return myType;
									}
								}
								else
								{
									msg = "Operation failed - Parking allowed once a week and not on friday and saturdays!";
									System.out.println(msg);
									myType.setObj(msg);
									myType.setSysMode(SystemMode.EMPTY_RES);
									return myType;
								}
									
							}
					}
				}
			}
		}
		return myType;
	}
	
	/**
	 * this method will find the optimal location for new car in the lot, using only the lot number.
	 * @param lotNum the number of the lot
	 * @return the serial number of the optimal parking space in the lot.
	 * @throws SQLException if connection with data base was unsuccessful
	 * @throws ParseException if parsing date not supported
	 */
	
	private int findOptimalLocation(int lotNum) throws SQLException, ParseException
	{
		int opt = 0, saved=0, i;
		DateTime myDate,d;
		SimpleDateFormat myTime = new SimpleDateFormat("HH:mm:ss");
		SimpleDateFormat arDate = new SimpleDateFormat("yyyy-MM-dd");
		DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
		String orderDate = null;
		String orderTime = null;
		if (order != null)
		{
			orderDate = order.getLeaveDate();
			orderTime = order.getExpectedLeaveTime();
			String source = orderDate + " " +orderTime;
			myDate = fmt.parseDateTime(source);
		}
		else
		{
			orderDate = client.getClientLeaveDate();
			orderTime = client.getClientLeaveTime();
			String source2 = orderDate + " " +orderTime;
			myDate = fmt.parseDateTime(source2);
		}
		for (i=1; i<4; i++)
		{
			opt = this.IsRowAvailable(lotNum, i);
			if (opt == -1)
			{
				opt = this.IsRowHasSaved(lotNum, i);
				if (opt == -1)
				{
					System.out.println(maxSid[lotNum-1][i-1]);
					if (maxSid[lotNum-1][i-1] <= 0)
						maxSid[lotNum-1][i-1] = this.findMaxFromOccupiedInRow(lotNum, i);
					d = this.getDateforSpace(maxSid[lotNum-1][i-1]);
					if (d.isAfter(myDate))
					{
						opt = maxSid[lotNum-1][i-1];
						if (i!=3)
							maxSid[lotNum-1][i]=opt;
						this.updateAfterLocationChange(opt, lotNum);
						return opt;
					}					
				}
				else
				{
					int temp = this.lastAvailable(lotNum);
					this.updateSpaceStatus(temp, "SAVED");
					return opt;
				}
			}
			else
				return opt;
		}
		return opt;
	}
	
	/**
	 * this method will do the needed changes after removing the wanted car from the lot.
	 * @param sid the serial number of the parking space which is now empty because we removed the car.
	 * @param lotNum the number of the lot
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private void updateLotAfterRemove(int sid,int lotNum) throws SQLException
	{
		ResultSet rs;
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE sid = "+sid+";");
		rs.first();
		int row = rs.getInt("rowNumber");
		if (row == 3)
			return;
		int temp = maxSid[lotNum-1][row];
		if (temp == 0||temp==-1)
			return;
		rs = stmt.executeQuery("SELECT * FROM vcp.vehicleinlocation WHERE parkingLocation_sid = "+temp+";");
		rs.next();
		int vehicle = rs.getInt(1);
		this.order = null;
		this.getOrder(vehicle, "CLOSED");
		if (order == null)
				this.getSubscription(vehicle, "VALID");
		this.updateAfterLocationChange(temp, lotNum);
		this.updateSpaceStatus(temp, "FREE");
		maxSid[lotNum-1][row] = this.findMaxFromOccupiedInRow(lotNum, row);
		maxSid[lotNum-1][row-1] = temp;
		if (row == 1)
		{
			temp = maxSid[lotNum-1][row+1];
			if (temp == 0||temp==-1)
				return;
			rs = stmt.executeQuery("SELECT * FROM vcp.vehicleinlocation WHERE parkingLocation_sid = "+temp+";");
			rs.next();
			vehicle = rs.getInt(1);
			this.order = null;
			this.getOrder(vehicle, "ACTIVE");
			if (order == null)
					this.getSubscription(vehicle, "VALID");
			this.updateAfterLocationChange(temp, lotNum);
			this.updateSpaceStatus(temp, "FREE");
			maxSid[lotNum-1][row+1] = this.findMaxFromOccupiedInRow(lotNum, row+1);
			maxSid[lotNum-1][row] = temp;
		}
	}
	
	/**
	 * this method will get the expected leave date of the car from the parking space
	 * @param sid - the serial number of the parking space
	 * @return the expected leave date
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private DateTime getDateforSpace(int sid) throws SQLException
	{
		ResultSet rs;
		DateTime d;
		String orderDate = null;
		String orderTime = null;
		DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE (sidParkingLocation = "+sid+" AND orderStatus = 'ACTIVE');");
		if (rs.next())
		{
			orderDate = rs.getString("leaveDate");
			orderTime = rs.getString("expectedLeaveTime");
			d = fmt.parseDateTime(orderDate + " " + orderTime);
			return d;
		}else
		{
			rs = stmt.executeQuery("SELECT * FROM vcp.vehicleinlocation WHERE sidParkingLocation = "+sid+";");
			rs.first();
			int v = rs.getInt(1);
			rs = stmt.executeQuery("SELECT * FROM vcp.subscribedclient WHERE vehicleNumber = "+v+";");
			rs.first();
			orderDate = rs.getString("clientleaveDate");
			orderTime = rs.getTime("clientLeaveTime").toString();
			d = fmt.parseDateTime(orderDate + " " + orderTime);
			return d;
		}
	}
	
	/**
	 * this method will find the space of the car with the latest leave date in all floors of the lot, in the same row.
	 * @param lotNum the lot number
	 * @param rowNum the row number
	 * @return the serial number of the parking Space
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private int findMaxFromOccupiedInRow(int lotNum,int rowNum) throws SQLException
	{
		ResultSet rs, rs1;
		int sid=0,opt=0,i=20;

		DateTime d,maxD;
		String orderDate = null;
		String orderTime = null;
		SimpleDateFormat thetime = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
		String date1 = thetime.format(new Date());
		DateTimeFormatter fmt = DateTimeFormat.forPattern("yyyy-MM-dd HH:mm:ss");
		maxD = fmt.parseDateTime(date1);
		stmt = db.getConn().createStatement();

		rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation AS p,vcp.order AS o WHERE (p.sid = o.sidParkingLocation AND o.orderStatus = 'ACTIVE' AND p.parkingSpaceStatus = 'OCCUPIED' AND p.rowNumber = "+rowNum+" AND p.lotNumber = "+lotNum+");");
		while (rs.next())
		{
			orderDate = rs.getString("leaveDate");
			orderTime = rs.getString("expectedLeaveTime");
			sid = rs.getInt(1);
			d = fmt.parseDateTime(orderDate + " " + orderTime);
			if (d.isAfter(maxD))
			{
				maxD = d;
				opt = sid;
			}
		}
		return ((opt == 0)?-1:opt);
	}
	
	/**
	 * this method will find the free parking space with the largest serial number.
	 * the largest the serial number is, the parking space will be higher and deeper in the lot. 
	 * @param lotNum the number of the lot
	 * @return the largest serial number currently free at the lot
	 * @throws SQLException if connection with data base was unsuccessful
	 */

	private int lastAvailable(int lotNum) throws SQLException
	{
		ResultSet rs;
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE lotNumber = '"+lotNum+"' AND parkingSpaceStatus ='FREE';");
		if (rs.last())
			return rs.getInt(1);
		return -1;
	}
	
	/**
	 * this method will check if there is available parking space in all floors in the same row
	 * @param lotNum the number of the lot
	 * @param rowNum the number of the row
	 * @return the serial number of the free space if any, -1 otherwise.
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private int IsRowAvailable(int lotNum,int rowNum) throws SQLException
	{
		ResultSet rs;
		stmt = db.getConn().createStatement();
		System.out.println(1);
		rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE (lotNumber = '"+lotNum+"' AND rowNumber = '"+rowNum+"' AND parkingSpaceStatus ='FREE');");
		System.out.println(112);
		if (rs.first())
			return rs.getInt(1);
		return -1;
	}
	
	/**
	 * this method will check if there is saved parking space in all floors in the same row
	 @param lotNum the lot number
	 * @param rowNum the row number
	 * @return the serial number of the parking Space, -1 otherwise
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private int IsRowHasSaved(int lotNum,int rowNum) throws SQLException
	{
		ResultSet rs;
		stmt = db.getConn().createStatement();
		System.out.println(2);
		rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE lotNumber = " +lotNum + " AND rowNumber = "+rowNum+" AND parkingSpaceStatus ='SAVED';");
		if (rs.last())
			return rs.getInt(1);
		return -1;
	}
	
	/**
	 * this method will update the status of given parking space
	 * @param sid the serial number of the parking space
	 * @param status the status to set
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private void updateSpaceStatus(int sid,String status) throws SQLException
	{
		stmt = db.getConn().createStatement();
		stmt.executeUpdate("UPDATE vcp.parkinglocation SET parkingSpaceStatus = '"+status+"' WHERE sid = "+sid+";");
	}
	
	/**
	 * this method will perform all needed updates after location change of vehicle.
	 * @param oSid the old serial number of the parking space which the car was in before
	 * @param lotNum the number of the lot
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private void updateAfterLocationChange(int oSid,int lotNum) throws SQLException
	{
		ResultSet rs;
		
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.parkinglocation WHERE lotNumber = " +lotNum + " AND parkingSpaceStatus = 'FREE';");
		rs.first();
		int newSid = rs.getInt(1);
		if (order!= null)
		{
			stmt.executeUpdate("UPDATE vcp.order SET sidParkingLocation = "+newSid+" WHERE (sidParkingLocation = "+oSid+" AND orderStatus = 'ACTIVE');");
			
			stmt.executeUpdate("UPDATE vcp.order SET sidParkingLocation = "+newSid+" WHERE orderNumber = "+order.getOrderNumber()+";");
		}
		else
			stmt.executeUpdate("UPDATE vcp.subscribedclient SET sidParkingLocation = "+newSid+" WHERE subscriptionNumber = "+client.getSubscriptionNumber()+";");
		stmt.executeUpdate("UPDATE vcp.vehicleinlocation SET parkingLocation_sid = "+newSid+" WHERE parkingLocation_sid = "+oSid+";");
		this.updateSpaceStatus(newSid, "OCCUPIED");
	}
	
	/**
	 * this method will perform all needed updates after inserting new car
	 * @param sid the serial number where we inserted the car into
	 * @param type order/subscription flag, as updates are different in each case
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private void updatesAfterInsert(int sid, String type) throws SQLException
	{
		this.updateSpaceStatus(sid, "OCCUPIED");
		SimpleDateFormat myTime = new SimpleDateFormat("HH:mm:ss");
		Date d = new Date();
		SimpleDateFormat thetime = new SimpleDateFormat("HH:mm:ss");
		String currTime = thetime.format(d);
		stmt = db.getConn().createStatement();
		if (type.equals("Order"))
		{
			System.out.println(11);			
			stmt.executeUpdate("UPDATE vcp.order SET orderStatus = 'ACTIVE' WHERE orderNumber = "+order.getOrderNumber()+";");
			System.out.println(12);
			stmt.executeUpdate("UPDATE vcp.order SET sidParkingLocation = '"+sid+"' WHERE orderNumber = "+order.getOrderNumber()+";");
			System.out.println(13);
			stmt.executeUpdate("INSERT INTO vcp.vehicleinlocation (`vehicleNumber`,`parkingLocation_sid`) VALUES ('"+order.getVehicleNumber()+"','"+sid+"');");
			System.out.println(14);
			System.out.println(currTime);
			if (order.getOrderType().equals("ADVANCE"))
				stmt.executeUpdate("UPDATE vcp.order SET arrivalTime = '"+currTime+"' WHERE orderNumber = "+order.getOrderNumber()+";");
			System.out.println(15);
		}
		else
		{
			if (client.getTypeOfSubscription().equals("Normal"))
			{
				DateTime d1 = new DateTime();
				Days diff = Days.daysBetween(d1, new DateTime(client.getLastParkingDate()));
				if (!diff.isGreaterThan(Days.ONE))
					stmt.executeUpdate("UPDATE vcp.subscribedclient SET subsequentDaysOfParking = subsequentDaysOfParking+1 WHERE subscriptionNumber = "+client.getSubscriptionNumber()+";");
			}
			stmt.executeUpdate("UPDATE vcp.subscribedclient SET sidParkingLocation = '"+sid+"' WHERE subscriptionNumber = "+client.getSubscriptionNumber()+";");
			stmt.executeUpdate("UPDATE vcp.subscribedclient SET lastParkingDate = '"+d.toString()+"' WHERE subscriptionNumber = "+client.getSubscriptionNumber()+";");
			stmt.executeUpdate("UPDATE vcp.subscribedclient SET numOfParkingUsed = numOfParkingUsed+1 WHERE subscriptionNumber = "+client.getSubscriptionNumber()+";");
			stmt.executeUpdate("INSERT INTO vcp.vehicleinlocation (`vehicleNumber`,`parkingLocation_sid`) VALUES ('"+client.getVehicleNumber()+"','"+sid+"');");
		}
	}
	
	/**
	this method will perform all needed updates after removing car
	 * @param sid the serial number where we removed the car from
	 * @throws SQLException if connection with data base was unsuccessful
	 */
	
	private void updatesAfterRemove(int sid) throws SQLException
	{
		stmt = db.getConn().createStatement();
		stmt.executeUpdate("DELETE FROM vcp.vehicleinlocation WHERE parkingLocation_sid = "+sid+";");
		this.updateSpaceStatus(sid, "FREE");
		this.updateLotAfterRemove(sid, lot);
	}
	

	/**
	 * this method will handle inserting of car to the lot
	 * @param myType the systemTypes object which can contain order or subscription
	 * @return the result of the operation
	 */
	
	public SystemTypes InsertVehicle(SystemTypes myType){
		int optSid = 0,lotNum;
		Serializable obj = myType.getObj();
		String type = "";
		
		
		try {
				myType = this.CheckValidity(myType);
				if (!myType.getSysMode().equals(SystemMode.OK))
					return myType;
				if (obj instanceof Order)
				{
					Order o = (Order) obj;
					this.getOrder(o.getVehicleNumber(),"OPEN");
					type = "Order";
				}
				else
				{
					SubscribedClient c = (SubscribedClient) obj;
					this.getSubscription(c.getVehicleNumber(),"VALID");
					type = "Subscription";
				}
				optSid = this.findOptimalLocation(lot);
				this.updatesAfterInsert(optSid, type);			
			} 
			catch (SQLException ex) 
			{
				System.out.println("SQLException: " + ex.getMessage());
				System.out.println("SQLState: " + ex.getSQLState());
				System.out.println("VendorError: " + ex.getErrorCode());
				myType.setSysMode(SystemMode.DB_CON_ERR);
			} catch (ParseException e) {
				e.printStackTrace();
				System.out.println("Exception: " + e.getMessage());
			}
		return myType;
	}
	
	/**
	 * this method will handle removing of car from the lot
	 * @param myType the systemTypes object which can contain order or subscription
	 * @return the result of the operation
	 */
	
	public SystemTypes RemoveVehicle(SystemTypes myType)
	{
		Serializable obj = myType.getObj();
		ResultSet rs;
		int vehicle,sid;
		try 
		{
			if (obj instanceof Order)
				vehicle = this.getVehicle(((Order)obj).getVehicleNumber());
			else
				vehicle = ((SubscribedClient)obj).getVehicleNumber();
			stmt = db.getConn().createStatement();
			rs = stmt.executeQuery("SELECT * FROM vcp.vehicleinlocation WHERE vehicleNumber = "+vehicle+";");
			System.out.println(vehicle);
			if (rs.first() == false)
			{
				myType.setObj("Operation Failed - car not in the lot!");
				myType.setSysMode(SystemMode.EMPTY_RES);
				return myType;
			}
			sid = rs.getInt(2);
			this.updatesAfterRemove(sid);
			myType.setObj("Operation ended succesfully!");
			myType.setSysMode(SystemMode.OK);
		} 
		catch (SQLException ex) 
		{
			System.out.println("SQLException: " + ex.getMessage());
			System.out.println("SQLState: " + ex.getSQLState());
			System.out.println("VendorError: " + ex.getErrorCode());
			myType.setSysMode(SystemMode.DB_CON_ERR);
		}
		return myType;
	}
	
	/**
	 * this method will bring the vehicle number of the requested order
	 * @param client - the order number
	 * @return the vehicle number
	 * @throws SQLException - if connection to the DB gone wrong
	 */
	
	private int getVehicle(int client) throws SQLException
	{
		int v;
		ResultSet rs;
		stmt = db.getConn().createStatement();
		rs = stmt.executeQuery("SELECT * FROM vcp.order WHERE orderNumber = "+client+";");
		rs.first();
		v = rs.getInt("vehicleNumber");
		return v;
	}
	

}
